package com.example.materials_management.repository;

import com.example.materials_management.entity.MaterialsBasicInfoEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface MaterialsBasicInfoRepository extends JpaRepository<MaterialsBasicInfoEntity, Integer> {
    @Query(value =
            "SELECT b.*, r.remaining_amount FROM materials_basic_info b LEFT JOIN materials_remaining_info r ON b.id=r.id" +
                    " WHERE (:id is null or b.id = :id)" +
                    " AND (:name is null or b.name LIKE %:name%)" +
                    " AND (:specification is null or b.specification LIKE %:specification%)" +
                    " AND (:category is null or b.category LIKE %:category%)" +
                    " AND (:measureUnit is null or b.measure_unit LIKE %:measureUnit%)" +
                    " AND (:remainingAmountMin is null or r.remaining_amount >= :remainingAmountMin)" +
                    " AND (:remainingAmountMax is null or r.remaining_amount <= :remainingAmountMax)",
            nativeQuery = true)
    List<Map<String, Object>> findItems(
            @Param("id") Integer id,
            @Param("name") String name,
            @Param("specification") String specification,
            @Param("category") String category,
            @Param("measureUnit") String measureUnit,
            @Param("remainingAmountMin") Integer remainingAmountMin,
            @Param("remainingAmountMax") Integer remainingAmountMax
    );

    @Query(value = "SELECT id FROM materials_basic_info", nativeQuery = true)
    List<Integer> getIds();
}
